import pymssql
import pymysql


class SqlServer:
    def __init__(self, server: str, user: str, password: str, database: str, port=1433):
        self.server = server
        self.user = user
        self.password = password
        self.database = database
        self.port = port

    def _link(self):
        connect = pymssql.connect(host=self.server, user=self.user, password=self.password, database=self.database,
                                  port=self.port)
        cursor = connect.cursor(as_dict=True)
        return connect, cursor

    def getOneData(self, sql: str):
        connect, cursor = self._link()
        return self._getResult(cursor.fetchone, sql, connect, cursor)

    def getAllData(self, sql: str):
        connect, cursor = self._link()
        return self._getResult(cursor.fetchall, sql, connect, cursor)

    @staticmethod
    def _getResult(request_type, sql, connect, cursor):
        try:
            cursor.execute(sql)
            result = request_type()
        except Exception as error:
            if type(error.args[0]) is int:
                raise Exception(str(error.args[1]) + '\t错误SQL:' + str(sql))
            else:
                raise Exception(str(error.args[0]) + '\t错误SQL:' + str(sql))
        finally:
            cursor.close()
            connect.close()
        return result

    """输入插入的内容，返回插入的数据的id值"""

    def addData(self, sql: str):
        sql += """;select @@IDENTITY as result"""
        return self._commit(sql)

    """输入更新的内容，返回受影响的行数"""

    def changeData(self, sql: str):
        sql += """;select @@ROWCOUNT as result"""
        return self._commit(sql)

    def _commit(self, sql):
        connect, cursor = self._link()
        try:
            cursor.execute(sql)
            result = cursor.fetchone()['result']
            connect.commit()
        except Exception as error:
            if type(error.args[0]) is int:
                raise Exception(str(error.args[1]) + '\t错误SQL:' + str(sql))
            else:
                raise Exception(str(error.args[0]) + '\t错误SQL:' + str(sql))
        finally:
            cursor.close()
            connect.close()
        return result

    def getSqlAll(self, sql_list):
        connect, cursor = self._link()
        sql = "暂无sql"
        result = []
        try:
            for sql in sql_list:
                cursor.execute(sql)
                result += cursor.fetchall()
        except Exception as error:
            if type(error.args[0]) is int:
                raise Exception(str(error.args[1]) + '\t错误SQL:' + str(sql))
            else:
                raise Exception(str(error.args[0]) + '\t错误SQL:' + str(sql))
        finally:
            cursor.close()
            connect.close()
            return result

    def getProc(self, proc_name, args):
        sql = "exec {} @ORDERID='{}'".format(proc_name, args)
        return self.getAllData(sql)


    def commitAll(self, sql_list):
        connect, cursor = self._link()
        sql = "暂无sql"
        try:
            if sql_list:
                for sql in sql_list:
                    cursor.execute(sql)
                connect.commit()
        except Exception as error:
            if type(error.args[0]) is int:
                raise Exception(str(error.args[1]) + '\t错误SQL:' + str(sql))
            else:
                raise Exception(str(error.args[0]) + '\t错误SQL:' + str(sql))
        finally:
            cursor.close()
            connect.close()

    def updateOrInsert(self, sql: str):
        if sql.lower().startswith("insert"):
            return self.addData(sql)
        else:
            return self.changeData(sql)


class MySql(SqlServer):
    def __init__(self, server: str, user: str, password: str, database: str, port=3306):
        super().__init__(server, user, password, database, port)

    def _link(self):
        self.connect = pymysql.connect(host=self.server, user=self.user, password=self.password,
                                       database=self.database,
                                       port=self.port)
        self.cursor = self.connect.cursor(cursor=pymysql.cursors.DictCursor)

    """输入插入的内容，返回插入的数据的id值"""

    def addData(self, sql: str):
        self._link()
        return self._commit(sql, self.connect.affected_rows)

    """输入更新的内容，返回受影响的行数"""

    def changeData(self, sql: str):
        self._link()
        return self._commit(sql, self.connect.affected_rows)

    def _commit(self, sql, result_method):
        try:
            self.cursor.execute(sql)
            result = result_method()
            self.connect.commit()
        except Exception as error:
            if type(error.args[0]) is int:
                raise Exception(str(error.args[1]) + '\t错误SQL:' + str(sql))
            else:
                raise Exception(str(error.args[0]) + '\t错误SQL:' + str(sql))
        finally:
            self.cursor.close()
            self.connect.close()
        return result
